USE [DSRSystem_Temp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPrepareTables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPrepareTables]
GO


CREATE PROCEDURE [dbo].[spPrepareTables]
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SyncLog') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[SyncLog](
			[Id] [int] IDENTITY(1,1) NOT NULL,
			[LastSyncDate] [datetime] NULL,
			[LastUpdate] [datetime] NULL,
			[ElapsedTime] [int] NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FinalizedCompanies') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[FinalizedCompanies](
		[CompanyId] uniqueidentifier NOT NULL,
	    [CompanyName] nvarchar(max) NOT NULL,
		[CapitalIQID] nvarchar(max) NOT NULL,
		[LastFinalUpdate] datetime NOT NULL,
	    [LastCentralizedResearchUpdate] datetime NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FinalizedIndicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[FinalizedIndicators](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[Name] nvarchar(max) NOT NULL,
			[Number] nvarchar(50) NOT NULL,
			[Description] nvarchar(1500) NULL,
			[Score] int NOT NULL,
			[Outlook] int NULL,
			[Tickedboxes] nvarchar(max) NULL,
			[Comments] nvarchar(max) NULL,
			[Sources] nvarchar(max) NULL

		) ON [PRIMARY]
	END
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.StageFinalizedIndicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[StageFinalizedIndicators](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[Name] nvarchar(max) NOT NULL,
			[Number] nvarchar(50) NOT NULL,
			[Description] nvarchar(1500) NULL,
			[Score] int NOT NULL,
			[Outlook] int NULL,
			[Tickedboxes] nvarchar(max) NULL,
			[Comments] nvarchar(max) NULL,
			[Sources] nvarchar(max) NULL

		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FinalizedSources') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[FinalizedSources](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[Code] nvarchar(max) NOT NULL,
			[Text] nvarchar(max) NOT NULL,
			[IndicatorsNumbers] nvarchar(max) NULL
		) ON [PRIMARY]
	END
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.StageFinalizedSources') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[StageFinalizedSources](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[Code] nvarchar(max) NOT NULL,
			[Text] nvarchar(max) NOT NULL,
			[IndicatorsNumbers] nvarchar(max) NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplates') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplates](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[IndicatorName] [nvarchar](max) NOT NULL,
		[IndicatorNumber] [nvarchar](max) NOT NULL,
		[IndicatorSortKey] [int] NOT NULL,
		[IssueName] [nvarchar](max) NOT NULL,
		[IssueNumber] int NOT NULL
		) ON [PRIMARY]
	END
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplateTickboxes') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplateTickboxes](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[TickboxId] [uniqueidentifier] NOT NULL,
		[Number] [nvarchar](max) NOT NULL,
		[Text] [nvarchar](max) NOT NULL
		) ON [PRIMARY]
	END
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplateAnswerCategories') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplateAnswerCategories](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[Code] [char](1) NOT NULL,
		[Text] [nvarchar](max) NOT NULL,
		[Score] int NOT NULL
		) ON [PRIMARY]
	END

END
GO
